#!/usr/bin/env python
# -*- coding: utf-8 -*-
# File  :overtime_statistics.py
# Time  :2025/4/11 9:20
# Email :fortunatedong@163.com
# Author:by Adonli
import os
from datetime import datetime
from collections import defaultdict
import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Alignment, Border, Side, Font, PatternFill
from openpyxl.utils import get_column_letter
from copy import copy
from config import ROOT_DIR
class OvertimeStatistics(object):
    def __init__(self,number,ding_hr_department_path,ding_hr_overtime_statistics_path,hr_overtime_statistics_path):
        # self.ding_hr_overtime_statistics_path = "C:/Users/yadong_li/Desktop/人事/20250410173800016.xlsx"
        # self.ding_hr_department_path = "C:/Users/yadong_li/Desktop/人事/公司人员部门统计表.xlsx"
        # self.hr_overtime_statistics_path = os.path.join(ROOT_DIR, 'Datas', 'hr_overtime_statistics_files.xlsx')
        self.number = number
        self.ding_hr_overtime_statistics_path = ding_hr_overtime_statistics_path
        self.ding_hr_department_path = ding_hr_department_path
        self.hr_overtime_statistics_path = hr_overtime_statistics_path
        self.ding_hr_overtime_statistics = pd.read_excel(self.ding_hr_overtime_statistics_path)
        self.ding_hr_department = pd.read_excel(self.ding_hr_department_path)
        self.zubienames = []
        self.sheetnames = []

    def clearn_hr_overtime_statistics(self):
        """
        匹配部门和组别
        """
        merage_hr_overtime_statistics = pd.merge(left=self.ding_hr_overtime_statistics, right=self.ding_hr_department, how='left',
                             left_on="加班人",
                             right_on="姓名")

        temp_hr_overtime_statistics = copy(merage_hr_overtime_statistics[
            ["审批记录(含处理人UserID)", "加班类型", "加班事由", "加班人", "开始时间", "结束时间", "加班时间",
             "加班时长", "时长", "部门", "组别"]])
        """
        拆分日期，用于后续合并条件
        """
        temp_hr_overtime_statistics.loc[:, "开始日期"] = pd.to_datetime(temp_hr_overtime_statistics["开始时间"]).dt.strftime("%Y-%m-%d")
        temp_hr_overtime_statistics.loc[:, "结束日期"] = pd.to_datetime(temp_hr_overtime_statistics["结束时间"]).dt.strftime("%Y-%m-%d")
        temp_hr_overtime_statistics.loc[:, "加班时间"] = pd.to_datetime(temp_hr_overtime_statistics["加班时间"],
                                                                        errors='coerce')
        """
        筛选本月的数据（测试时是上一个月的数据current_month-1）
        """
        today = datetime.today()
        current_year = today.year
        current_month = today.month
        filtered_hr_overtime_statistics = temp_hr_overtime_statistics[
            (pd.to_datetime(temp_hr_overtime_statistics['开始时间']).dt.year == current_year) &
            (pd.to_datetime(temp_hr_overtime_statistics['开始时间']).dt.month == current_month - self.number) &
            (
                    (pd.to_datetime(temp_hr_overtime_statistics['加班时间']).dt.month == (current_month - self.number)) |
                    (temp_hr_overtime_statistics['加班时间'].isna())  # 直接使用isna()，不要再用pd.to_datetime包装
            )
            ]

        hr_overtime_statistics = filtered_hr_overtime_statistics.sort_values(["组别", "加班人", "开始时间"])

        zubienames = hr_overtime_statistics["组别"].unique()
        sheetnames = [f"{current_year}-{current_month-self.number}{i}加班记录" for i in zubienames]
        self.zubienames = zubienames
        self.sheetnames = sheetnames
        return hr_overtime_statistics
    def save_hr_overtime_statistics(self):
        hr_overtime_statistics = self.clearn_hr_overtime_statistics()
        hr_overtime_statistics.to_excel(self.hr_overtime_statistics_path,index=False, sheet_name='加班汇总表',engine='openpyxl')
        # 创建 ExcelWriter 对象
        with pd.ExcelWriter(self.hr_overtime_statistics_path, engine='openpyxl') as writer:
            # 先写入第一个 Sheet
            hr_overtime_statistics.to_excel(writer, sheet_name='加班汇总表', index=False)
            # 获取工作簿对象
            workbook = writer.book
            # 删除 openpyxl 自动创建的默认空 Sheet
            if 'Sheet' in workbook.sheetnames:
                del workbook['Sheet']
            # 创建Sheet
            for sheet_name in self.sheetnames:
                if sheet_name not in workbook.sheetnames:
                    workbook.create_sheet(sheet_name)

    def load_hr_overtime_statistics(self):
        """
         在汇总表中将需要合并的单元格进行合并
         然后写入汇总表中
        """
        self.save_hr_overtime_statistics()
        wb = load_workbook(self.hr_overtime_statistics_path)
        ws = wb["加班汇总表"]
        end_row = ws.max_row
        current_key = None
        start_row = 2
        merge_ranges = []
        def serch_column_index(target_column_name):
            column_index = None
            # 遍历第一行查找列名
            for col in range(1, ws.max_column + 1):
                cell_value = ws.cell(row=1, column=col).value  # 获取第一行的单元格值
                if cell_value == target_column_name:
                    column_index = col
                    break
            return column_index
        name_column_name = "加班人"
        name_column_index = serch_column_index(name_column_name)
        zubie_column = "组别"
        zubie_column_index = serch_column_index(zubie_column)
        start_date_column = "开始日期"
        start_date_column_index = serch_column_index(start_date_column)
        duration_column = "时长"
        duration_column_index = serch_column_index(duration_column)
        # print(name_column_index, start_date_column_index, zubie_column_index,duration_column_index)
        for row in range(start_row, end_row+1):
            name = ws.cell(row=row, column=name_column_index).value
            zubie = ws.cell(row=row, column=zubie_column_index).value
            start_date = ws.cell(row=row, column=start_date_column_index).value
            current_row_key = (name, zubie, start_date)
            # print(current_row_key)
            # 如果是新分组
            if current_row_key != current_key:
                # 记录上一个需要合并的区域（至少2行才需要合并）
                if current_key is not None and (row - 1) > start_row:
                    merge_ranges.append((start_row, row - 1, ws.cell(row=start_row, column=duration_column_index).value))
                # 重置跟踪变量
                current_key = current_row_key
                start_row = row
        # print(merge_ranges)
            # 处理最后一组可能的合并
            if start_row < row:
                merge_ranges.append((start_row, row, ws.cell(row=start_row, column=duration_column_index).value))
        # 逆序合并单元格（避免行号变化导致问题）
        for start, end, value in reversed(merge_ranges):
            # 合并时长列
            ws.merge_cells(start_row=start, end_row=end, start_column=duration_column_index, end_column=duration_column_index)
            # 设置合并后的值（取合并区域第一行的值）
            ws.cell(row=start, column=duration_column_index).value = value
            # 设置居中对齐
            ws.cell(row=start, column=duration_column_index).alignment = Alignment(horizontal='center', vertical='center')
        wb.save(self.hr_overtime_statistics_path)

        """
        分组写入对应sheetname
        """
        def copy_cell_style(src_cell, dst_cell):
            """复制单元格样式（字体、边框、填充等）"""
            if src_cell.has_style:
                dst_cell.font = copy(src_cell.font)
                dst_cell.border = copy(src_cell.border)
                dst_cell.fill = copy(src_cell.fill)
                dst_cell.number_format = copy(src_cell.number_format)
                dst_cell.alignment = copy(src_cell.alignment)

        def process_overtime_records(group_sheets):
            src_sheet = wb['加班汇总表']

            # 遍历每个组别
            for group_name, target_sheet_name in group_sheets:
                print(f"\n[DEBUG] 正在处理组别: {group_name} => {target_sheet_name}")  # 调试输出

                # 筛选数据行（修正逻辑）
                group_rows = []
                for row in src_sheet.iter_rows(min_row=2):
                    cell_value = str(row[zubie_column_index - 1].value).strip()  # 列索引从0开始
                    if cell_value == group_name:
                        group_rows.append(row)
                print(f"[DEBUG] 找到 {len(group_rows)} 行数据")  # 调试输出

                # 创建/清空目标Sheet（修正清空方式）
                if target_sheet_name in wb.sheetnames:
                    target_sheet = wb[target_sheet_name]
                    # 清空现有数据（保留格式）
                    for row in reversed(range(1, target_sheet.max_row + 1)):
                        target_sheet.delete_rows(row)
                    for col in reversed(range(1, target_sheet.max_column + 1)):
                        target_sheet.delete_cols(col)
                else:
                    target_sheet = wb.create_sheet(target_sheet_name)

                # 复制标题行（优化样式复制）
                for col_idx, cell in enumerate(src_sheet[1], 1):
                    target_cell = target_sheet.cell(row=1, column=col_idx, value=cell.value)
                    copy_cell_style(cell, target_cell)

                # 复制数据行（带合并信息）
                row_mapping = {}
                for dest_row_idx, src_row in enumerate(group_rows, start=2):
                    # 记录源行号映射
                    row_mapping[src_row[0].row] = dest_row_idx

                    # 复制单元格数据及样式
                    for col_idx, cell in enumerate(src_row, start=1):
                        target_cell = target_sheet.cell(
                            row=dest_row_idx,
                            column=col_idx,
                            value=cell.value
                        )
                        copy_cell_style(cell, target_cell)

                # 处理合并单元格（优化逻辑）
                for merge_range in src_sheet.merged_cells.ranges:
                    # 检查是否属于当前组
                    min_row = merge_range.min_row
                    max_row = merge_range.max_row
                    min_col = merge_range.min_col
                    max_col = merge_range.max_col

                    # 查找相关行
                    relevant_rows = [
                        row_num for row_num in range(min_row, max_row + 1)
                        if row_num in row_mapping
                    ]

                    if relevant_rows:
                        # 转换行号
                        dest_min_row = min(row_mapping[row] for row in relevant_rows)
                        dest_max_row = max(row_mapping[row] for row in relevant_rows)

                        # 执行合并
                        target_sheet.merge_cells(
                            start_row=dest_min_row,
                            end_row=dest_max_row,
                            start_column=min_col,
                            end_column=max_col
                        )
                        # 复制样式
                        src_cell = src_sheet.cell(row=min_row, column=min_col)
                        target_cell = target_sheet.cell(row=dest_min_row, column=min_col)
                        copy_cell_style(src_cell, target_cell)

                # 调整列宽
                for col in src_sheet.columns:
                    col_letter = get_column_letter(col[0].column)
                    target_sheet.column_dimensions[col_letter].width = \
                        src_sheet.column_dimensions[col_letter].width or 15
            # 最终保存（确保只保存一次）
            wb.save(self.hr_overtime_statistics_path)
        group_sheets = []
        for i in zip(self.zubienames,self.sheetnames):
            group_sheets.append(i)
        # print(group_sheets)
        process_overtime_records(group_sheets)

        def load_group_sheets():
            for group_name, target_sheet_name in group_sheets:
                target_sheet = wb[target_sheet_name]
                name_column_letter = get_column_letter(name_column_index)
                duration_column_letter = get_column_letter(duration_column_index)
                # 获取所有名字（忽略空值）
                names = [cell.value for cell in target_sheet[name_column_letter] if cell.value is not None]
                # print(names)
                #统计名字出现次数
                name_count = defaultdict(int)
                duration_sum = defaultdict(float)
                for row_idx, name in enumerate(names, 1):
                    name_count[name] += 1
                    duration = target_sheet[f"{duration_column_letter}{row_idx}"].value
                    if isinstance(duration, (int, float)):
                        duration_sum[name] += float(duration)
                print(name_count,duration_sum)
                merged_cells_ranges = target_sheet.merged_cells.ranges
                # print(merged_cells_range)
                rows_count = 1
                max_row_add = {}
                for key,value in name_count.items():
                    if key != "加班人":
                        rows_count+=1
                        rows_count += value
                        max_row_add[key] = rows_count
                        # 在插入行之前，先处理合并单元格
                        # 1. 取消所有受影响合并单元格
                        # 创建合并单元格范围的副本，避免迭代时修改原始集合
                        merged_cells_ranges = list(target_sheet.merged_cells.ranges)  # 转换为列表副本
                        new_merged_ranges = []
                        for merged_range in merged_cells_ranges:
                            min_row = merged_range.min_row
                            max_row = merged_range.max_row
                            # 如果合并单元格在插入行之后，需要调整其行号
                            if min_row >= rows_count:
                                # 取消合并并重新合并到新位置
                                target_sheet.unmerge_cells(str(merged_range))
                                new_min_row = min_row + 1
                                new_max_row = max_row + 1
                                new_range = f"{get_column_letter(merged_range.min_col)}{new_min_row}:{get_column_letter(merged_range.max_col)}{new_max_row}"
                                new_merged_ranges.append(new_range)
                            elif max_row >= rows_count:
                                # 如果插入行在合并单元格内部，扩展合并范围
                                target_sheet.unmerge_cells(str(merged_range))
                                new_min_row = min_row
                                new_max_row = max_row + 1
                                new_range = f"{get_column_letter(merged_range.min_col)}{new_min_row}:{get_column_letter(merged_range.max_col)}{new_max_row}"
                                new_merged_ranges.append(new_range)
                            else:
                                # 不受影响的合并单元格保持不变
                                new_merged_ranges.append(str(merged_range))
                        target_sheet.insert_rows(rows_count)
                        target_sheet.merge_cells(
                            start_row=rows_count,
                            end_row=rows_count,
                            start_column=1,
                            end_column=10
                        )
                        target_sheet.cell(row=rows_count, column=1).value = f"{key}总加班时长："
                        # 设置居中对齐
                        target_sheet.cell(row=rows_count, column=1).alignment = Alignment(horizontal='right', vertical='center')
                        target_sheet.row_dimensions[rows_count].height = 18
                        target_sheet.cell(row=rows_count, column=1).font = Font(name='宋体',size=12, bold=True,italic=False,color='000000')
                        # target_sheet.cell(row=rows_count, column=1).fill = PatternFill(fgColor='FDE9D9', fill_type="solid")


                        target_sheet.merge_cells(
                            start_row=rows_count,
                            end_row=rows_count,
                            start_column=11,
                            end_column=13
                        )
                        target_sheet.cell(row=rows_count, column=11).value = duration_sum[key]
                        # 设置居中对齐
                        target_sheet.cell(row=rows_count, column=11).alignment = Alignment(horizontal='left',vertical='center')
                        target_sheet.row_dimensions[rows_count].height = 18
                        target_sheet.cell(row=rows_count, column=11).font = Font(name='宋体', size=12, bold=True, italic=False, color='000000')
                        # target_sheet.cell(row=rows_count, column=11).fill = PatternFill(fgColor='FDE9D9', fill_type="solid")

                        # 2. 重新应用合并单元格
                        for range_str in new_merged_ranges:
                            target_sheet.merge_cells(range_str)
                # 设置标题行样式
                target_sheet.row_dimensions[1].height = 18
                for i in range(1,14):
                    target_sheet.cell(row=1, column=i).fill = PatternFill(fgColor='92CDDC',fill_type="solid")
                    target_sheet.cell(row=1, column=i).font = Font(name='宋体', size=15, bold=True,italic=False, color='000000')
                    target_sheet.cell(row=1, column=i).alignment = Alignment(horizontal='center',vertical='center')
            wb.save(self.hr_overtime_statistics_path)
        load_group_sheets()
        return {"status":0,"message":"分析已完成"}

# if __name__ == '__main__':
#     ding_current_hr_overtime_statistics_path = os.path.join(ROOT_DIR,
#                                                             'Datas/hr_overtime_statistics_files/current_hr_overtime_statistics', )
#     hr_overtime_statistics_result_path = os.path.join(ROOT_DIR,
#                                                       'Datas/hr_overtime_statistics_files/hr_overtime_statistics_result', )
#     number = 1
#     hr_overtime_statistics_path = f"{hr_overtime_statistics_result_path}/结果统计hr_overtime_statistics.xlsx"
#     ding_hr_department_path = f"{ding_current_hr_overtime_statistics_path}/公司人员部门统计表.xlsx"
#     ding_hr_overtime_statistics_path = f"{ding_current_hr_overtime_statistics_path}/hr_overtime_statistics.xlsx"
#     OvertimeStatistics(number,ding_hr_department_path,ding_hr_overtime_statistics_path,hr_overtime_statistics_path).load_hr_overtime_statistics()

